<?php 
class DboperationsController extends AppController {
	
	var $name = 'Dboperations';
	public $components = array('Remakedate', 'Session');
	
	function index() {
		
	}
	function addstudents() {
		// подгружаем модели
		$this->loadModel('Facultet');
		$this->loadModel('Profession');
		$this->loadModel('Course');
		$this->loadModel('Studentgroup');
		$this->loadModel('Student');
		$this->loadModel('Studentinfo');
		$this->loadModel('Visa');
		$this->loadModel('Medcomission');
		$this->loadModel('Contract');
		$this->loadModel('Mideduadequacydoc');
		$this->loadModel('Order');
		$this->loadModel('Hostels_studentinfo');
		$this->loadModel('Migrationcard');
		$this->loadModel('Studentinsurance');
		$this->loadModel('Registration');
		
		$nondate = 'нет данных';
		$nonchislo = '2020-01-01';
		$errors = array(); //массив с ошибками
		$loggetfile = $this->request->data["Dboperation"]["csvfile"];
		//debug($loggetfile);
		$csvfile = fopen($loggetfile["tmp_name"],"r");
		//читаем первую строку там заголовки
		$line = fgetcsv($csvfile, 10000, ";");
		
		//debug($line);
		while(!(feof($csvfile))) {
			//$line = array();
			$line = fgetcsv($csvfile, 10000, ";"); 
			/*foreach ($liner as $element):
			    array_push($line, iconv('Windows-1251', 'UTF-8', $element));
			endforeach;*/
			//debug($line);
			if ($line) {
				$flag = 0; //флаг ошибок
				if ($line[14]) {
				  $group = $line[14];
				if (preg_match("/[0-9]/", $group[2])) {
					$groupname = $group[1].$group[2].' группа';
					$groupseason = $group[3];
				  } else {
					$groupname = $group[1].' группа';
					$groupseason = $group[2];
				  }
				  if (preg_match("/[E,Е]/", $group[0])){
				      $grouplanguage = 2;
				      $groupname = $groupname.' (eng)';
				  } elseif ($group[0] == 'R') {
					  $grouplanguage = 1;
					  $groupname = $groupname.' (rus)';
				  } else {
					  array_push($line, 'Ошибка в группе');
					  $flag++;
				  }
				  
				//debug($line[4]);
				$cor = $line[13];
				if (preg_match("/[A,А]/", $groupseason)) {
				    $coursename = $cor[0]." курс (осень)";
				} elseif ($groupseason == 'S') {
					$coursename = $cor[0]." курс (весна)";
				} else {
					array_push($line, 'Ошибка в группе');
					$flag++;
				}
				//debug($coursename);
				if ($flag == 0) {
				   $courses = $this->Course->find('all', array('conditions' => 
				   array('Course.profession_id' => $line[12], 'Course.name' => $coursename), 
				   'recursive' => -1, 'order' => 'Course.id'));
				   //debug($courses);
				   $groups = $this->Studentgroup->find('all', array('conditions' => 
				   array('Studentgroup.course_id' => $courses[0]['Course']['id'], 'Studentgroup.name' => $groupname, 
				   'Studentgroup.studentgroupstype_id' => $grouplanguage),'recursive' => -1, 'order' => 'Studentgroup.id'));
				   //debug($line[4]);
				   if ($groups) {
				   	  if ($line[9]) {
				   	  	$studentstate_id = $line[9];
				   	  } else {
				   	  	array_push($line, 'Нет состояния студента');
					    $flag++;
				   	  }
				      if ($line[10]) {
				   	  	$learningtype_id = $line[10];
				   	  } else {
				   	  	array_push($line, 'Нет типа обучения');
					    $flag++;
				   	  }
				      if ($line[5]) {
				   	  	$lastname = $line[5];
				   	  } else {
				   	  	$lastname = '';
				   	  }
				      if ($line[6]) {
				   	  	$firstname = $line[6];
				   	  } else {
				   	  	array_push($line, 'Нет имени');
				   	  	$firstname = '';
					    $flag++;
				   	  }
				   	  $fio = $firstname.' '.$lastname;
				      if ($line[3]) {
				   	  	$eng_lastname = $line[3];
				   	  } else {
				   	  	$eng_lastname = '';
				   	  }
				      if ($line[4]) {
				   	  	$eng_firstname = $line[4];
				   	  } else {
				   	  	array_push($line, 'Нет имени');
				   	  	$eng_firstname = '';
					    $flag++;
				   	  }
				   	  $eng_fio = $eng_firstname.' '.$eng_lastname;
				   	
				   	
				   	  $this->Student->create();
		              $newstudent = array ('Student' => array(
														'learningtype_id' => $learningtype_id,
														'studentgroup_id' => $groups[0]['Studentgroup']['id'],
		                                                'fio' => $fio,
		                                                
		                                                'eng_fio' => $eng_fio,
		                                                
		                                                'studentstate_id' => $studentstate_id,
		                                                'unic_number' => '',
		                                                'info' => ''));
		              $this->Student->save($newstudent);
				      $student_id = $this->Student->id;
			          //debug($line);
			          if (!$student_id) {
			   	        array_push($line, 'Студент не записан');
			   	        $flag++;
			          } else {
			                if ($line[2]) {
				   	  	       $citizenship_id = $line[2];
				   	        } else {
				   	  	       array_push($line, 'Нет гражданства');
				   	  	       $citizenship_id = 1;
					           $flag++;
				   	        }
			                if ($line[15]) {
				   	  	       $enrollmentdate = $this->Remakedate->remake($line[15]);
				   	        } else {
				   	  	       array_push($line, 'Нет даты поступления');
				   	  	       $enrollmentdate = $nonchislo;
					           $flag++;
				   	        }
			                if ($line[7]) {
				   	  	       $passport_birthdate = $this->Remakedate->remake($line[7]);
				   	        } else {
				   	  	       array_push($line, 'Нет даты рождения');
				   	  	       $passport_birthdate = $nonchislo;
					           $flag++;
				   	        }
			                if ($line[16]) {
				   	  	       $passport_birthplace = $line[16];
				   	        } else {
				   	  	       $passport_birthplace = '';
				   	        }
			                if ($line[18]) {
				   	  	       $passport_number = $line[18];
				   	        } else {
				   	  	       array_push($line, 'Нет номера паспорта');
				   	  	       $passport_number = $nondate;
					           $flag++;
				   	        }
			                if ($line[19]) {
				   	  	       $phone = $line[19];
				   	        } else {
				   	  	       $phone = '';
				   	        }
			                if ($line[20]) {
				   	  	       $mobile = $line[20];
				   	        } else {
				   	  	       $mobile = '';
				   	        }
			                if ($line[21]) {
				   	  	       $email = $line[21];
				   	        } else {
				   	  	       $email = '';
				   	        }
			                if ($line[22]) {
				   	  	       $moreinfo = $line[22];
				   	        } else {
				   	  	       $moreinfo = '';
				   	        }
			                if ($line[8]) {
				   	  	       $educationline_id = $line[8];
				   	        } else {
				   	  	       array_push($line, 'Нет линии обучения');
				   	  	       $educationline_id = 1;
					           $flag++;
				   	        }
				   	        if ($line[51]) {
				   	            $number_acc = $line[51];
				   	        } else {
				   	        	$number_acc = 1;
				   	        }
				   	        if ($line[50]) {
				   	            $number_eduline = $line[50];
				   	        } else {
				   	        	$number_eduline = 1;
				   	        }
				   	        
				   	        
			          	
			          	    $newstudentinfodata = array('Studentinfo' => array(
														'student_id' => $student_id,
														'gender_id' => 1,
														'citizenship_id' => $citizenship_id,
														'foreignlanguage_id' => 1,
			          	                                'number_acc' => $number_acc,
			          	                                'number_eduline' => $number_eduline,
														'fio' => $fio,
														
														'eng_fio' => $eng_fio,
			          	                                
														'enrollmentdate' => $enrollmentdate,
			          	                                'passport_birthplace' => $passport_birthplace,
														'passport_birthdate' => $passport_birthdate,
														'passport_number' => $passport_number, 
														'phone' => $phone,
														'mobile' => $mobile,
														'email' => $email,
														'moreinfo' => $moreinfo,
			          	                                'educationline_id' => $educationline_id
							));		
							$this->Studentinfo->create();
							$this->Studentinfo->save($newstudentinfodata);
			                $studentinfo_id = $this->Studentinfo->id;
			                //debug($line);
			                if (!$studentinfo_id) {
			   	               array_push($line, 'Студентинфо не записано');
			   	               $flag++;
			                }
			                //записываем визу
			                if ($line[23] and $line[24] and $line[25]) {
			                	$issue_date = $this->Remakedate->remake($line[23]);
			                	$entry_from = $this->Remakedate->remake($line[24]);
			                	$stay_until = $this->Remakedate->remake($line[25]);
			                	if ($line[26]) {
			                		$period = $line[26];
			                	} else {
			                		$period = '';
			                	}
			                    if ($line[27]) {
			                		$purpose = $line[27];
			                	} else {
			                		$purpose = '';
			                	}
			                    if ($line[28]) {
			                		$limmited_by = $line[28];
			                	} else {
			                		$limmited_by = '';
			                	}
			                    $this->Visa->create();
		                        $newvisa = array (      'Visa' => array(
														'studentinfo_id' => $studentinfo_id,
														'visastype_id' => 1,
		                                                'issue_date' => $issue_date,
		                                                'entry_from' => $entry_from,
		                                                'stay_until' => $stay_until,
		                                                'period' => $period,
		                                                'purpose' => $purpose,
		                                                'limmited_by' => $limmited_by,
		                                                'info' => '' ));
		                        $this->Visa->save($newvisa);
				                $visa_id = $this->Visa->id;
			                    //debug($line);
			                    if (!$visa_id) {
			   	                    array_push($line, 'Виза не записана');
			   	                    $flag++;
			                    }
			                } /*else {
			                	array_push($line, 'Нехватает данных по визе');
					            $flag++;
			                }*/
			                //записываем регистрацию
			                if ($line[29] and $line[30]) {
			                	$adress = $line[29];
			                	$stay_until = $this->Remakedate->remake($line[30]);
			                    $this->Registration->create();
		                        $newregistration = array('Registration' => array(
														'studentinfo_id' => $studentinfo_id,
														'identifydoctype_id' => 1,
		                                                'adress' => $adress,
		                                                'stay_until' => $stay_until ));
		                        $this->Registration->save($newregistration);
				                $registration_id = $this->Registration->id;
			                    //debug($line);
			                    if (!$registration_id) {
			   	                    array_push($line, 'Регисрация не записана');
			   	                    $flag++;
			                    }
			                }
			                //записываем медкомисию
			                if ($line[31] and $line[32]) {
			                	$from = $this->Remakedate->remake($line[31]);
			                	$until = $this->Remakedate->remake($line[32]);
			                    $this->Medcomission->create();
		                        $newmedcomission = array('Medcomission' => array(
														'studentinfo_id' => $studentinfo_id,
														'year' => 2012,
		                                                'from' => $from,
		                                                'until' => $until ));
		                        $this->Medcomission->save($newmedcomission);
				                $medcomission_id = $this->Medcomission->id;
			                    //debug($line);
			                    if (!$medcomission_id) {
			   	                    array_push($line, 'Медкомиссия не записана');
			   	                    $flag++;
			                    }
			                }
			                //записываем страховку
			                if ($line[33]) {
			                	$insurance_id = $line[33];
			                	if ($line[34]) {
			                	    $info = $line[34];
			                	} else {
			                		$info = $nondate;
			                	}
			                	if ($line[35]) {
			                	    $from = $this->Remakedate->remake($line[35]);
			                	} else {
			                		$from = $nonchislo;
			                	}
			                	if ($line[36]) {
			                	    $until = $this->Remakedate->remake($line[36]);
			                	} else {
			                		$until = $nonchislo;
			                	}
			                    $this->Studentinsurance->create();
		                        $newstudentinsurance = array('Studentinsurance' => array(
														'studentinfo_id' => $studentinfo_id,
														'insurance_id' => $insurance_id,
		                                                'info' => $info,
		                                                'from' => $from,
		                                                'until' => $until ));
		                        $this->Studentinsurance->save($newstudentinsurance);
				                $studentinsurance_id = $this->Studentinsurance->id;
			                    //debug($line);
			                    if (!$studentinsurance_id) {
			   	                    array_push($line, 'Страховка не записана');
			   	                    $flag++;
			                    }
			                }
			                //записываем контракт
			                if ($line[37] and $line[38] and $line[39]) {
			                    $info = $line[37];
			                	$from = $this->Remakedate->remake($line[38]);
			                	$until = $this->Remakedate->remake($line[39]);
			                	$this->Contract->create();
		                        $newcontract = array('Contract' => array(
														'studentinfo_id' => $studentinfo_id,
														'info' => $info,
		                                                'from' => $from,
		                                                'until' => $until ));
		                        $this->Contract->save($newcontract);
				                $contract_id = $this->Contract->id;
			                    //debug($line);
			                    if (!$contract_id) {
			   	                    array_push($line, 'Контракт не записан');
			   	                    $flag++;
			                    }
			                }
			                //записываем документ соответствия образования
			                if ($line[45]) {
			                	$adequacy = $line[45];
			                	if ($line[46]) {
			                	   $info = $line[46];
			                	} else {
			                	   $info = $nondate;
			                	}
			                    $this->Mideduadequacydoc->create();
		                        $newmideduadequacydoc = array('Mideduadequacydoc' => array(
														'studentinfo_id' => $studentinfo_id,
														'info' => $info,
		                                                'inprocess' => 0,
		                                                'adequacy' => $adequacy ));
		                        $this->Mideduadequacydoc->save($newmideduadequacydoc);
				                $mideduadequacydoc_id = $this->Mideduadequacydoc->id;
			                    //debug($line);
			                    if (!$mideduadequacydoc_id) {
			   	                    array_push($line, 'Документ соответствия образования не записан');
			   	                    $flag++;
			                    }
			                }
			                //записываем миграционную карту
			                if ($line[47] and $line[48] and $line[49]) {
			                	$ser = $line[47];
			                	$num = $line[48];
			                	$entrydate = $this->Remakedate->remake($line[49]);
			                    $this->Migrationcard->create();
		                        $newmigrationcard = array('Migrationcard' => array(
														'studentinfo_id' => $studentinfo_id,
														'entrydate' => $entrydate,
		                                                'ser' => $ser,
		                                                'num' => $num ));
		                        $this->Migrationcard->save($newmigrationcard);
				                $migrationcard_id = $this->Migrationcard->id;
			                    //debug($line);
			                    if (!$migrationcard_id) {
			   	                    array_push($line, 'Миграционная карта не записана');
			   	                    $flag++;
			                    }
			                	
			                }
			                //записываем общежитие
			                $hostelnamber = $line[52];
			                if ($line[52] and preg_match("/[1-5]/", $hostelnamber[0])) {
			                	$hostel_id = $hostelnamber;
			                	if ($line[53]) {
			                	   $room = $line[53];
			                	} else {
			                	   $room = $nondate;
			                	}
			                	$info = $nondate;
			                	$paid = $nonchislo;
			                    $this->Hostels_studentinfo->create();
		                        $newhostelsstudentinfo = array('Hostels_studentinfo' => array(
														'studentinfo_id' => $studentinfo_id,
														'hostel_id' => $hostel_id,
		                                                'room' => $room,
		                                                'info' => $info,
		                                                'paid' => $paid ));
		                        $this->Hostels_studentinfo->save($newhostelsstudentinfo);
				                $hostelsstudentinfo_id = $this->Hostels_studentinfo->id;
			                    //debug($line);
			                    if (!$hostelsstudentinfo_id) {
			   	                    array_push($line, 'Общежитие не записано');
			   	                    $flag++;
			                    }
			                }
			                //записываем приказы
			                $orders = array();
			                if ($line[55]) {
			                	array_push($orders, '01'.'55');
			                }
			                if ($line[57]) {
			                	array_push($orders, '09'.'57');
			                }
			                if ($line[59]) {
			                	array_push($orders, '10'.'59');
			                }
			                if ($line[61]) {
			                	array_push($orders, '11'.'61');
			                }
			                if ($line[63]) {
			                	array_push($orders, '12'.'63');
			                }
			                if ($line[65]) {
			                	array_push($orders, '13'.'65');
			                }
			                if ($line[67]) {
			                	array_push($orders, '16'.'67');
			                }
			                if ($line[69]) {
			                	array_push($orders, '03'.'69');
			                }
			                if ($line[71]) {
			                	array_push($orders, '19'.'71');
			                }
			                if ($line[73]) {
			                	array_push($orders, '04'.'73');
			                }
			                if ($orders) {
			                   foreach ($orders as $element):
			                       $ordertype_id = $element[0].$element[1];
			                       $aaa = $element[2].$element[3];
			                       $number = $line[$aaa];
			                       if ($line[$aaa + 1]) {
			                          $date = $this->Remakedate->remake($line[$aaa + 1]);
			                       } else {
			                       	  $date = $nonchislo;
			                       }
			                       $this->Order->create();
		                           $neworder = array('Order' => array(
														'student_id' => $student_id,
														'ordertype_id' => $ordertype_id,
		                                                'number' => $number,
		                                                'date' => $date,
		                                                'order_info' => '' ));
		                           $this->Order->save($neworder);
				                   $order_id = $this->Order->id;
			                       //debug($line);
			                       if (!$order_id) {
			   	                      array_push($line, 'Приказ не записан');
			   	                      $flag++;
			                       }
			                   endforeach;
			                }
			          
			          
			          }
			          
			          
				   } else {
				   	  array_push($line, 'Группа не найдена');
					  $flag++;
				   }

				}
				
				} else {
					array_push($line, 'Группа отсутствует в файле');
					$flag++;	
				}
			  if ($flag > 0)	{
			      array_push($errors, $line);
		      }
		      //debug($line);
		      //debug($errors); 
			}
			
		}
		//debug($errors);
		$this->set('errors',$errors);
        //$this->render('/', 'blank', 'addstudents');
		
		
	}
	
	function addgroups() {
		// подгружаем модели
		$this->loadModel('Facultet');
		$this->loadModel('Profession');
		$this->loadModel('Course');
		$this->loadModel('Studentgroup');
		$nondate = 'нет данных';
		$nonchislo = '2020-01-01';
		$errors = array(); //массив с ошибками
		$loggetfile = $this->request->data["Dboperation"]["csvfile"];
		//debug($loggetfile);
		$csvfile = fopen($loggetfile["tmp_name"],"r");
		//читаем первую строку там заголовки
		$line = fgetcsv($csvfile, 10000, ";");
		/*$rty = '01.09.2011';
		$dgdg = $this->Remakedate->remake($rty);
		debug($dgdg);*/
		
		//debug($line);
		while(!(feof($csvfile))) {
			$line = array();
			$liner = fgetcsv($csvfile, 10000, ";"); 
			foreach ($liner as $element):
			    array_push($line, iconv('Windows-1251', 'UTF-8', $element));
			endforeach;
			
			//debug($line);
			if ($line) {
				//debug($line);
				$flag = 0; //флаг ошибок
				if ($line[14]) {
				$group = $line[14];
				if (preg_match("/[E,Е]/", $group[0])){
				    $grouplanguage = 2;
				} elseif ($group[0] == 'R') {
					$grouplanguage = 1;
				} else {
					array_push($line, 'Ошибка в группе');
					$flag++;
				}
				  if (preg_match("/[0-9]/", $group[2])) {
					$groupname = $group[1].$group[2].' группа';
					$groupnamer = $group[1].$group[2];
					$groupseason = $group[3];
				  } else {
					$groupname = $group[1].' группа';
					$groupnamer = '0'.$group[1];
					$groupseason = $group[2];
				  }
				  switch ($line[13]) {
				  	case 1: $year = 2011; break;
				  	case 2: $year = 2010; break;
				  	case 3: $year = 2009; break;
				  	case 4: $year = 2008; break;
				  	case 5: $year = 2007; break;
				  	case 6: $year = 2006; break;
				  }
				  $cor = $line[13];
				  if (preg_match("/[A,А]/", $groupseason)) {
				      $coursename = $cor[0]." курс (осень)";
				  } elseif ($groupseason == 'S') {
					  $coursename = $cor[0]." курс (весна)";
					  $year++;
				  } else {
					  array_push($line, 'Ошибка в группе');
					  $flag++;
				  }
				  
				  
				  $bbb = $line[15];
				  $groupnamber = $year.'.'.$groupnamer.'.'.'0'.$line[12];
				  //debug($groupnamber);
				  if ($flag == 0) {
				  	$courses = $this->Course->find('all', array('conditions' => 
				   array('Course.profession_id' => $line[12], 'Course.name' => $coursename), 
				   'recursive' => -1, 'order' => 'Course.id'));
				   //debug($courses);
				   $groups = $this->Studentgroup->find('all', array('conditions' => 
				   array('Studentgroup.course_id' => $courses[0]['Course']['id'], 'Studentgroup.name' => $groupname,
				   'Studentgroup.studentgroupstype_id' => $grouplanguage), 'recursive' => -1, 'order' => 'Studentgroup.id'));
				   if (!$groups) {
				      $this->Studentgroup->create();
		              $newstudentgroup = array ('Studentgroup' => array(
														'course_id' => $courses[0]['Course']['id'],
														'learningform_id' => $line[11],
		                                                'learningtype_id' => $line[10],
		                                                'studentgroupstype_id' => $grouplanguage,
		                                                'number' => $groupnamber,
		                                                'name' => $groupname ));
		              $this->Studentgroup->save($newstudentgroup);
				      $studentgroup_id = $this->Studentgroup->id;
			          //debug($line);
			          if (!$studentgroup_id) {
			   	        array_push($line, 'Группа не записана');
			   	        $flag++;
			          }
				   }
				  }
				} else {
					array_push($line, 'Группа отсутствует в файле');
					$flag++;
				}
			    if ($flag > 0)	{
			      array_push($errors, $line);
		      }
			}
		}
		$this->set('errors',$errors);
        //$this->render('/', 'blank', 'addgroups');
		
	}
	
	function addfile() {
		$sdf = $this->request->data["Dboperation"]["file"];
		debug($sdf);
		$k = 0;
		if(is_uploaded_file($sdf["tmp_name"])) {
			$k = 23;
		}
		debug($k);
		move_uploaded_file($sdf["tmp_name"], "/students/".$sdf["name"]);
	}
	
	
	
}
?>